
drop table send_message;
drop table receive_message;

create table send_message(
	message_no number primary key,
	title varchar2(50) not null,
	content varchar2(1000) not null,
	w_date date not null,
	to_uid number ,
	from_uid number ,--닉네임으로교체
	constraint fk_send_toid foreign key(to_uid) references member(m_uid) on delete set null,
	constraint fk_send_fromid foreign key(from_uid) references member(m_uid) on delete set null
);

create table receive_message(
	message_no number primary key,
	title varchar2(50) not null,
	content varchar2(1000) not null,
	w_date date not null,
	to_uid number ,--닉네임으로교체
	from_uid number ,
	constraint fk_receive_toid foreign key(to_uid) references member(m_uid) on delete set null,
	constraint fk_receive_fromid foreign key(from_uid) references member(m_uid) on delete set null
);

drop sequence send_message_seq;
drop sequence receive_message_seq;

create sequence send_message_seq;
create sequence receive_message_seq;

------------- 여기까지 실행 ------------------

insert into send_message(message_no, title, content, w_date, to_uid, from_uid)
values(send_message_seq.nextval, 'test', 'test', sysdate, 1, 3);
insert into receive_message(message_no, title, content, w_date, to_uid, from_uid)
values(receive_message_seq.nextval, 'test', 'test', sysdate, 1, 3);

insert into message(message_no, title, content, w_date, to_uid, from_uid)
values(message_seq.nextval, 'test', 'test', sysdate, 4, 3);

select * from send_MESSAGE;
select * from receive_message;

select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, to_uid, from_uid, mem.m_nickname as nickname
from receive_message mess, member mem
where mess.from_uid=mem.m_uid and to_uid=1

select message_no, title, w_date, to_uid, from_uid, nickname from(
select message_no, title, w_date, to_uid, from_uid, nickname, ceil(rownum/5) as page from(
	select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, to_uid, from_uid, mem.m_nickname as nickname
	from receive_message mess, member mem
	where mess.from_uid=mem.m_uid and to_uid=3 order by message_no desc
))where page=1

select message_no, title, w_date, to_uid, from_uid, nickname from(
select message_no, title, w_date, to_uid, from_uid, nickname, ceil(rownum/5) as page from(
	select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, to_uid, from_uid, mem.m_nickname as nickname
	from send_message mess, member mem
	where mess.to_uid=mem.m_uid and from_uid=3 order by message_no desc
))where page=1

select to_char(sysdate,'YYYY.MM.DD HH24:MI:SS') from dual

select count(*) from message where from_uid=1

select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, content, to_uid, from_uid, mem.m_nickname as nickname
from send_message mess, member mem
where mess.to_uid=mem.m_uid and message_no=5



select message_no, title, w_date, to_uid, from_uid, nickname from(
			select message_no, title, w_date, to_uid, from_uid, nickname, ceil(rownum/5) as page from(
				select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, to_uid, from_uid, mem.m_nickname as nickname
				from send_message mess, member mem
				where mess.from_uid=mem.m_uid and to_uid=null order by message_no desc
			)
		)where page=1
		
		
-------------------메세지 정보 가져오기
select * 
from send_message s 
left outer join
member t
on s.to_uid=t.m_uid
left outer join
member f
on s.from_uid=f.m_uid
		
		
		
		
=======
drop table send_message;
drop table receive_message;

create table send_message(
	message_no number primary key,
	title varchar2(50) not null,
	content varchar2(1000) not null,
	w_date date not null,
	to_uid number ,
	from_uid number ,--닉네임으로교체
	constraint fk_send_toid foreign key(to_uid) references member(m_uid) on delete set null,
	constraint fk_send_fromid foreign key(from_uid) references member(m_uid) on delete set null
);

create table receive_message(
	message_no number primary key,
	title varchar2(50) not null,
	content varchar2(1000) not null,
	w_date date not null,
	to_uid number ,--닉네임으로교체
	from_uid number ,
	constraint fk_receive_toid foreign key(to_uid) references member(m_uid) on delete set null,
	constraint fk_receive_fromid foreign key(from_uid) references member(m_uid) on delete set null
);

drop sequence send_message_seq;
drop sequence receive_message_seq;

create sequence send_message_seq;
create sequence receive_message_seq;

------------- 여기까지 실행 ------------------

insert into send_message(message_no, title, content, w_date, to_uid, from_uid)
values(send_message_seq.nextval, 'test', 'test', sysdate, 1, 3);
insert into receive_message(message_no, title, content, w_date, to_uid, from_uid)
values(receive_message_seq.nextval, 'test', 'test', sysdate, 1, 3);

insert into message(message_no, title, content, w_date, to_uid, from_uid)
values(message_seq.nextval, 'test', 'test', sysdate, 4, 3);

select * from send_MESSAGE;
select * from receive_message;

select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, to_uid, from_uid, mem.m_nickname as nickname
from receive_message mess, member mem
where mess.from_uid=mem.m_uid and to_uid=1

select message_no, title, w_date, to_uid, from_uid, nickname from(
select message_no, title, w_date, to_uid, from_uid, nickname, ceil(rownum/5) as page from(
	select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, to_uid, from_uid, mem.m_nickname as nickname
	from receive_message mess, member mem
	where mess.from_uid=mem.m_uid and to_uid=3 order by message_no desc
))where page=1

select message_no, title, w_date, to_uid, from_uid, nickname from(
select message_no, title, w_date, to_uid, from_uid, nickname, ceil(rownum/5) as page from(
	select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, to_uid, from_uid, mem.m_nickname as nickname
	from send_message mess, member mem
	where mess.to_uid=mem.m_uid and from_uid=3 order by message_no desc
))where page=1

select to_char(sysdate,'YYYY.MM.DD HH24:MI:SS') from dual

select count(*) from message where from_uid=1

select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, content, to_uid, from_uid, mem.m_nickname as nickname
from send_message mess, member mem
where mess.to_uid=mem.m_uid and message_no=5



select message_no, title, w_date, to_uid, from_uid, nickname from(
			select message_no, title, w_date, to_uid, from_uid, nickname, ceil(rownum/5) as page from(
				select message_no, title, to_char(w_date, 'YYYY.MM.DD HH24:MI:SS') as w_date, to_uid, from_uid, mem.m_nickname as nickname
				from send_message mess, member mem
				where mess.from_uid=mem.m_uid and to_uid=null order by message_no desc
			)
		)where page=1
		
		
-------------------메세지 정보 가져오기
select * 
from send_message s 
left outer join
member t
on s.to_uid=t.m_uid
left outer join
member f
on s.from_uid=f.m_uid
		
